﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_my_20141023]
	(
		@CompanyId int,
		@UserName nvarchar(50),
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stype nvarchar(50),
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@NOE_Flag int,
		@DateType int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	DECLARE @cSql nvarchar(4000),@cRq VARCHAR(20)
	set @cSql = ''
--条件判断开始
	
If @StateId <>0  --从大于0改为不等于0
	Set @cSql=@cSql+' AND StateId='+Convert(varchar(10),@StateId)
	
If @TypeId>0
	Set @cSql=@cSql+' AND TypeId='+Convert(varchar(10),@TypeId)
	
If @WayId>0
	Set @cSql=@cSql+' AND WayId='+Convert(varchar(10),@WayId)
	
If @ProcessId>0
	Set @cSql=@cSql+' AND ProcessId='+Convert(varchar(10),@ProcessId)
	
If @Stext<>''
	SET @cSql=@cSql+' AND '+@Stype+' like ''%'+@Stext+'%'' '
	
If @NOE_Flag<>100
	Set @cSql=@cSql+' AND NOE_Flag='+Convert(varchar(10),@NOE_Flag)
	
If @DateType=1
	Set @cRq = 'FinishDate'
Else
	Set @cRq = 'CreateDate'
	
Set @cSql = @cSql + ' And '+@cRq+'>=@sRq And '+@cRq+'<=@eRq'
--条件判断结束

	Set @cSql=@cSql+')'
	Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
;WITH list As(Select ROW_NUMBER() OVER (ORDER BY CreateDate Desc,Id DESC)AS Row
			,Id
			,OId
			,OrderId
			,CusId
			,CusName
			,CusPerson
			,CusPersonTel
			,CusDescription
			,CusEstimate
			,TypeId
			,TypeName
			,WayId
			,WayName
			,ProcessId
			,ProcessName
			,Salesman
			,SalesmanAccount
			,IsNull(FinishDate,''1900-01-01'') As TradeDate
			,CreateDate
			,YingShouPrice
			,IsNull(ShiShouPrice,0) as ShiShouPrice
			,Discount
			,PaymentTypeId
			,PaymentType
			,BankId
			,Bank
			,PaymentDescription
			,OtherDescription
			,DepId
			,CompanyId
			,StateId
			,IsNULL((Select Title From Order_State Where Id=[Order].StateId),''---'') As StateName
			,NOE_Flag
			
			--,IsNULL((select Pizhu from Customer_PiZhu where CustomerId=CusId),'''') as Pizhu --批注内容字段 2015.08.20 
		 --   ,IsNULL((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
		    
			From [Order]
			Where SalesmanAccount='''+@UserName+''' And CompanyId='+Convert(varchar(10),@CompanyId)+' '+@cSql
			
	Set @cSql=@cSql+'Select *,(Select Count(0) From list) As RecordCount From list Where Row Between '+Convert(varchar(10),@StartIndex)+' and '+Convert(varchar(10),@EndIndex) + ' Order By Row'
	Exec(@cSql)
	
	
--If @DateType=1	--完结时间
--	Begin
--		;WITH list As(Select ROW_NUMBER() OVER (ORDER BY CreateDate Desc,Id DESC)AS Row
--			,Id
--			,OId
--			,OrderId
--			,CusId
--			,CusName
--			,CusPerson
--			,CusPersonTel
--			,CusDescription
--			,CusEstimate
--			,TypeId
--			,TypeName
--			,WayId
--			,WayName
--			,ProcessId
--			,ProcessName
--			,Salesman
--			,SalesmanAccount
--			,IsNull(FinishDate,'1900-01-01') As TradeDate
--			,CreateDate
--			,YingShouPrice
--			,IsNull(ShiShouPrice,0) as ShiShouPrice
--			,Discount
--			,PaymentTypeId
--			,PaymentType
--			,BankId
--			,Bank
--			,PaymentDescription
--			,OtherDescription
--			,DepId
--			,CompanyId
--			,StateId
--			,IsNULL((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
--			,NOE_Flag
--			From [Order]
--			Where SalesmanAccount=@UserName And CompanyId=@CompanyId
--			And (@StateId=0 Or StateId=@StateId) and (@TypeId=0 Or TypeId=@TypeId) and (@WayId=0 Or WayId=@WayId) and (@ProcessId=0 Or ProcessId=@ProcessId)
----			And (Convert(nchar(10),CreateDate,120) Between @StartDate and @EndDate)
--			And FinishDate>=@sRq And FinishDate<=@eRq
--			And (@Stext='' Or CusName like '%'+@Stext+'%')
--			And ((@NOE_Flag=100 And NOE_Flag<>0) Or NOE_Flag=@NOE_Flag)
--		)

--		Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex and @EndIndex Order By Row
--	End
--Else	--报备时间
--	Begin
--		;WITH list As(Select ROW_NUMBER() OVER (ORDER BY CreateDate Desc,Id DESC)AS Row
--			,Id
--			,OId
--			,OrderId
--			,CusId
--			,CusName
--			,CusPerson
--			,CusPersonTel
--			,CusDescription
--			,CusEstimate
--			,TypeId
--			,TypeName
--			,WayId
--			,WayName
--			,ProcessId
--			,ProcessName
--			,Salesman
--			,SalesmanAccount
--			,IsNull(FinishDate,'1900-01-01') As TradeDate
--			,CreateDate
--			,YingShouPrice
--			,IsNull(ShiShouPrice,0) as ShiShouPrice
--			,Discount
--			,PaymentTypeId
--			,PaymentType
--			,BankId
--			,Bank
--			,PaymentDescription
--			,OtherDescription
--			,DepId
--			,CompanyId
--			,StateId
--			,IsNULL((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
--			,NOE_Flag
--			From [Order]
--			Where SalesmanAccount=@UserName And CompanyId=@CompanyId
--			And (@StateId=0 Or StateId=@StateId) and (@TypeId=0 Or TypeId=@TypeId) and (@WayId=0 Or WayId=@WayId) and (@ProcessId=0 Or ProcessId=@ProcessId)
----			And (Convert(nchar(10),CreateDate,120) Between @StartDate and @EndDate)
--			And CreateDate>=@sRq And CreateDate<=@eRq
--			And (@Stext='' Or CusName like '%'+@Stext+'%')
--			And ((@NOE_Flag=100 And NOE_Flag<>0) Or NOE_Flag=@NOE_Flag)
--		)

--		Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex and @EndIndex Order By Row
--	End
End
